/**
 * @Title: ChangChengChuKouDataProcess.java
 * @Package tbg 
 * @Description: TODO
 * @author tbc
 * @date 2016年6月9日 上午12:39:34
 * @version
 */
package tbg;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import java.util.concurrent.TimeUnit;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * h:\\Users\\tbc\\Desktop\\长城1605-出口 - 副本.xlsx
 * 
 * @author tbc
 * @version 1.0 create:{2016年6月9日 上午12:39:34}
 * 
 */
public class ChangChengChuKouDataProcess {
	static Logger log = LoggerFactory.getLogger(ChangChengChuKouDataProcess.class);

	static int staticRows = 3;
	static int staticColumns = 4;

	static XSSFWorkbook book = null;
	static XSSFSheet sheet = null;

	public static void main(String[] args) throws InterruptedException {
		@SuppressWarnings("resource")
		Scanner s = new Scanner(System.in);
		String from = s.nextLine();
		System.out.println("初始化 ...");
		// 初始化
		init(from);
		System.out.println("初始化完成");
		System.out.println("读取并处理数据...");
		List<List<String>> datas = readExcel();
		System.out.println("读取完成...处理完成...\n正在生成新的sheet页\"整理\"");
		// write
		write(datas);

		System.out.println("新的sheet页\"整理\"创建完成...\n正在写到Excel文件...");
		OutputStream out = null;
		try {
			out = new FileOutputStream(from);
			book.write(out);
		} catch (FileNotFoundException e) {
			System.out.println("创建输出流失败 ：" + e.getMessage());
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("写Excel失败 ： " + e.getMessage());
			e.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		System.out.println("完成");
		TimeUnit.SECONDS.sleep(1);

	}

	public static void init(String from) throws InterruptedException {
		if (from.startsWith("\"")) {
			System.out.println("文件路径带引号，尝试去除...");
			from = from.substring(1, from.length() - 1);
		}
		System.out.println("正在加载文件：" + from);

		InputStream in = null;
		try {
			in = new FileInputStream(from);
			System.out.println("加载成功，创建工作簿对象...");
			book = new XSSFWorkbook(in);
			System.out.println("工作簿对象创建完成");
		} catch (FileNotFoundException e) {
			System.out.println("读取文件失败，文件不存在或者文件名有特殊符号。。。最好是只有英文字母");
			System.out.println("程序自动退出，修改文件路径或者文件名再试试");
			TimeUnit.SECONDS.sleep(2);
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("加载excel失败，不知道为什么 ，程序退出。。。");
			System.out.println(e.getMessage());
			TimeUnit.SECONDS.sleep(2);
		}
		System.out.println("读取sheet页\"1\"");
		try {
			sheet = book.getSheet("1");
			if (sheet == null) {
				System.out.println("读取sheet页\"1\"失败，检查是否存在该sheet页");
				TimeUnit.SECONDS.sleep(2);
				System.exit(0);
			}
		} catch (Exception e) {
			System.out.println();
		}

	}

	public static List<List<String>> readExcel() {
		List<List<String>> result = new ArrayList<>();
		XSSFRow row0 = sheet.getRow(0);
		XSSFRow row1 = sheet.getRow(1);
		XSSFRow row2 = sheet.getRow(2);

		// List<XSSFRow> rows = new ArrayList<>();
		// for (int i = 0; i < staticRows; i++) {
		// rows.add(sheet.getRow(i));
		// }

		String c1 = "";
		String c2 = "";
		String c3 = "";
		// List<String> c = new ArrayList<>();

		for (int i = 3; i <= sheet.getLastRowNum(); i++) {
			System.out.println("row :" + i);

			XSSFRow row = sheet.getRow(i);
			for (int j = staticColumns; j < row.getLastCellNum(); j++) {
				System.out.println("column : " + j);
				XSSFCell cell = row.getCell(j);

				if (j == staticColumns) {
					c1 = row0.getCell(j).getStringCellValue();
					c2 = row1.getCell(j).getStringCellValue();
					c3 = row2.getCell(j).getStringCellValue();
				}
				if (cell == null || cell.getRawValue() == null)
					continue;

				List<String> temp = new ArrayList<>();

				// 首先判断对应的行一、行二、行三是否有值，缓存
				if (row0.getCell(j).getRawValue() != null) {
					c1 = row0.getCell(j).getStringCellValue();
				}
				if (row1.getCell(j).getRawValue() != null) {
					c2 = row1.getCell(j).getStringCellValue();
				}
				if (row2.getCell(j).getRawValue() != null) {
					c3 = row2.getCell(j).getStringCellValue();
				}
				// for (int x = 0; x < 3;)

				// 前四列加上
				temp.add(row.getCell(0).getStringCellValue());
				temp.add(row.getCell(1).getStringCellValue());
				temp.add(row.getCell(2).getStringCellValue());
				temp.add(row.getCell(3).getStringCellValue());
				// for (int y = 0; y < 4; y++) {
				// temp.add(row.getCell(y).getStringCellValue());
				// }

				// 紧跟三列
				// for (int z = 0; z < staticRows; z++) {
				// temp.add(c.get(z));
				// }
				temp.add(c1);
				temp.add(c2);
				temp.add(c3);

				// 最后才是真正的数据
				temp.add(Double.valueOf(cell.getNumericCellValue()).toString());

				result.add(temp);
			}
		}

		return result;
	}

	public static void write(List<List<String>> datas) {
		XSSFSheet mySheet = book.createSheet("整理");

		// 向book对象中写数据
		for (int i = 0; i < datas.size(); i++) {
			XSSFRow row = mySheet.createRow(i);
			List<String> ds = datas.get(i);
			for (int j = 0; j < ds.size(); j++) {
				XSSFCell cell = row.createCell(j);
				if (j != (ds.size() - 1)) {
					cell.setCellValue(ds.get(j));
				} else {
					String v = ds.get(j);
					double value = Double.parseDouble(v);
					cell.setCellValue(value);
				}

			}
		}
	}
}
